const Book = require("../model/booksTest.model")
 const borrow = require("../model/BorrowRecord.model")
const { where, Op } = require("sequelize");
const pool = require('../db/pool');


const seq = require('../db/seq');
class BooksServer {
    // 创建
    async createBooks(data) {
        // todo:写入数据库
        const res = await Book.create({
            ...data
        })
        return res
    }
    // 根据id查找
    async findBooksId(bookId) {
        try {
            const results = await Book.findAll({
                where: {
                    id: bookId
                }
            });
            return results;
        } catch (error) {
            console.error('根据ID查找书本:', error);
            throw error; // 根据需要处理错误
        }
    }
    // 模糊查询
    async findBooks(data) {
        try {
            const results = await Book.findAll({
                where: {
                    is_deleted: 0, // 确保只选择未删除的记录
                    [Op.or]: [
                        { title: { [Op.like]: `%${data}%` } },
                        { author: { [Op.like]: `%${data}%` } },
                        { publisher: { [Op.like]: `%${data}%` } },
                    ]
                }
            });
            return results;
        } catch (error) {
            console.error('错误', error);
            throw error; // 根据需要处理错误
        }
    }
    // 修改书籍
    async updateBook(bookId, newData) {
        try {
            const [updated] = await Book.update(newData, {
                where: { id: bookId }
            });

            if (updated) {
                console.log(`Book with ID ${bookId} was updated successfully.`);
                return true;
            } else {
                console.log(`Book with ID ${bookId} not found.`);
                return false;
            }
        } catch (error) {
            console.error('Error updating book:', error);
            throw error; // 处理错误
        }
    }
    // 删除书籍ID
    async deleteBookById(bookId) {
        try {
            const result= await Book.update({ is_deleted: 1 }, { where: { id: bookId } });
           
            console.log(result,22)
            if (result ===0) {
                console.log('没有找到书籍.');
                return "22"
            } else {
                return 1
            }
        } catch (error) {
            console.error('Error during book deletion:', error);
        }
    }
    // 批量删除
    async deleteBooksByIdsWithTransaction(bookIds) {
        try {
            const result = await Book.destroy({
                where: {
                    id: {
                        [Op.in]: bookIds // 使用 Op.in 来匹配多个 ID
                    }
                }
            });

            if (result === 0) {
                console.log('No books found with the given IDs.');
            } else {

                console.log(`${result} books deleted successfully.`);
                return 1
            }
        } catch (error) {
            console.error('Error during books deletion:', error);
        }
    }
    // 统计书籍给管理员端 这本书籍共几本 剩余几本
    async countgetBooks(pageSize, pageNumber) {
        const results = await Book.findAll({
            attributes: [
                'title', "author", "publisher", 'publish_year', 'source', 'category', 'created_at',
                [seq.fn('COUNT', seq.col('*')), 'total_count'],
                [seq.fn('SUM', seq.col('is_borrowed')), 'borrowed_count'],
            ],
            group: ['title'],
            limit: pageSize,
            offset: pageNumber,
        });
        return results;
    }
    // dataBooks 添加书籍
    async addb(data) {
        // todo:写入数据库
        const res = await Book.create({
            ...data
        })
        return res
    }


    // 分页查询书籍
    async bd(limit, offset) {
        // todo:写入数据库
        limit = Number(limit)
        offset = Number(offset)
        try {
            const [rows] = await pool.query(`
                SELECT * FROM bookser 
                WHERE is_deleted = 0 
                ORDER BY id ASC 
                LIMIT ? OFFSET ?`, [limit, offset]);
                const [[counts]] = await pool.query(`
                    SELECT COUNT(*) AS total_records 
                    FROM bookser 
                    WHERE is_deleted = 0;
                `);
            return {rows,counts};
        } catch (error) {
            console.error("查询错误:", error);
            return null; // 返回 null 或其他值以便调用方处理
        }
    }
}



module.exports = new BooksServer();